package com.DreamTeam.daoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.DreamTeam.dao.IEmployeeDao;
import com.DreamTeam.po.Employee;

public class EmployeeDao implements IEmployeeDao {
	private Connection conn = null;

	public EmployeeDao(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doAdd(Employee employee) {
		String sql = "insert into employee values(?,?,?,?,?,?,?,?,?,?)";
		int flag = 0;
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, employee.getEmployeeNo());
			prsmt.setString(2, employee.getEmployeeName());
			prsmt.setString(3, employee.getSex());
			prsmt.setDate(4,
					new java.sql.Date(employee.getBirthday().getTime()));
			prsmt.setString(5, employee.getAddress());
			prsmt.setString(6, employee.getTelephone());
			prsmt.setDate(7,
					new java.sql.Date(employee.getHiredate().getTime()));
			prsmt.setString(8, employee.getDepartment());
			prsmt.setString(9, employee.getHeadship());
			prsmt.setDouble(10, employee.getSalary());
			flag = prsmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (flag > 0)
			return true;
		else
			return false;
	}

	@Override
	public boolean doUpdate(Employee employee) {
		String sql = "update employee set EmployeeNo=? ,EmployeeName=?,Sex=?,Birthday=?,"
				+ "Address=?,Telephone=?,Hiredate=?,Department=?,Headship=?,Salary=? where EmployeeNo=?";
		int flag = 0;
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, employee.getEmployeeNo());
			prsmt.setString(2, employee.getEmployeeName());
			prsmt.setString(3, employee.getSex());
			prsmt.setDate(4,
					new java.sql.Date(employee.getBirthday().getTime()));
			prsmt.setString(5, employee.getAddress());
			prsmt.setString(6, employee.getTelephone());
			prsmt.setDate(7,
					new java.sql.Date(employee.getHiredate().getTime()));
			prsmt.setString(8, employee.getDepartment());
			prsmt.setString(9, employee.getHeadship());
			prsmt.setFloat(10, employee.getSalary());
			prsmt.setString(11, employee.getEmployeeNo());
			flag = prsmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (flag > 0)
			return true;
		else

			return false;
	}

	@Override
	public boolean doDelete(Employee employee) {
		String sql = "delete from employee where EmployeeNo=?";
		int flag = 0;
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, employee.getEmployeeNo());
			flag = prsmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (flag > 0)
			return true;
		else

			return false;
	}

	@Override
	public List<Employee> queryEmployeeByColumn(String keyword,
			String columnName, String orderColumn, String model) {
		String sql = "select * from employee where " + columnName
				+ " like ? order by " + orderColumn + " " + model;
		PreparedStatement prsmt = null;
		List<Employee> list = new ArrayList();
		try {

			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, "%" + keyword + "%");
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Employee employee = new Employee();
				employee.setEmployeeNo(re.getString(1));
				employee.setEmployeeName(re.getString(2));
				employee.setSex(re.getString(3));
				employee.setBirthday(re.getDate(4));
				employee.setAddress(re.getString(5));
				employee.setTelephone(re.getString(6));
				employee.setHiredate(re.getDate(7));
				employee.setDepartment(re.getString(8));
				employee.setHeadship(re.getString(9));
				employee.setSalary(re.getInt(10));
				list.add(employee);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Employee> queryEmployeeAll() {
		String sql = "select * from employee";
		List<Employee> list = new ArrayList();
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Employee employee = new Employee();
				employee.setEmployeeNo(re.getString(1));
				employee.setEmployeeName(re.getString(2));
				employee.setSex(re.getString(3));
				employee.setBirthday(re.getDate(4));
				employee.setAddress(re.getString(5));
				employee.setTelephone(re.getString(6));
				employee.setHiredate(re.getDate(7));
				employee.setDepartment(re.getString(8));
				employee.setHeadship(re.getString(9));
				employee.setSalary(re.getInt(10));
				list.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Employee> queryOrderByColumnName(String columnName, String model) {
		String sql = "select * from employee order by " + columnName + " "
				+ model;
		List<Employee> list = new ArrayList();
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Employee employee = new Employee();
				employee.setEmployeeNo(re.getString(1));
				employee.setEmployeeName(re.getString(2));
				employee.setSex(re.getString(3));
				employee.setBirthday(re.getDate(4));
				employee.setAddress(re.getString(5));
				employee.setTelephone(re.getString(6));
				employee.setHiredate(re.getDate(7));
				employee.setDepartment(re.getString(8));
				employee.setHeadship(re.getString(9));
				employee.setSalary(re.getInt(10));
				list.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Employee> queryByKeyword(String keyword) {
		String sql = "select * from employee where employeeNo like ? " +
				"or employeeName like ? or sex like ? or birthday like ? " +
				"or address like ? or telephone like ? or hiredate like ? " +
				"or department like ? or headship like ? or salary like ?";
		List<Employee> list = new ArrayList();
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, "%"+keyword+"%");
			prsmt.setString(2, "%"+keyword+"%");
			prsmt.setString(3, "%"+keyword+"%");
			prsmt.setString(4, "%"+keyword+"%");
			prsmt.setString(5, "%"+keyword+"%");
			prsmt.setString(6, "%"+keyword+"%");
			prsmt.setString(7, "%"+keyword+"%");
			prsmt.setString(8, "%"+keyword+"%");
			prsmt.setString(9, "%"+keyword+"%");
			prsmt.setString(10, "%"+keyword+"%");
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Employee employee = new Employee();
				employee.setEmployeeNo(re.getString(1));
				employee.setEmployeeName(re.getString(2));
				employee.setSex(re.getString(3));
				employee.setBirthday(re.getDate(4));
				employee.setAddress(re.getString(5));
				employee.setTelephone(re.getString(6));
				employee.setHiredate(re.getDate(7));
				employee.setDepartment(re.getString(8));
				employee.setHeadship(re.getString(9));
				employee.setSalary(re.getInt(10));
				list.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	

}
